﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QualityEvaluatingTools.model;
using Aspose.Cells;
using System.Windows.Forms;
using QualityEvaluatingTools.manager;

namespace QualityEvaluatingTools.helper
{
    /// <summary>
    /// excel帮助类
    /// </summary>
    public class ExcelHelper
    {
        #region 功能方法

        /// <summary>
        /// 获取所有考试学员的答案
        /// </summary>
        /// <param name="excelPath">excel路径</param>
        /// <returns></returns>
        public static List<Evaluation> GetStuEvaluationWithAnwer(string excelPath)
        {
            List<Evaluation> EvaluationList = new List<Evaluation>();
            try
            {
                Workbook workbook = new Workbook();
                workbook.Open(excelPath);
                //获得一个worksheet
                Worksheet worksheet = workbook.Worksheets[0];
                //获得worksheet中的cell集合
                Cells cells = worksheet.Cells;
                if (cells == null)
                    return null;
                for (int col = 0; col < cells.Rows.Count - 1; col++)
                {
                    Evaluation temp = GetEvaluation(cells, col);
                    if (temp != null)
                    {
                        EvaluationList.Add(temp);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return EvaluationList;
        }

        /// <summary>
        /// 导出综合测评汇总excel
        /// </summary>
        /// <param name="gridView"></param>
        /// <param name="savePath"></param>
        /// <returns></returns>
        public static bool ExportDataGridview(DataGridView gridView, string savePath)
        {
            Workbook outBook = new Workbook();

            string path = System.IO.Directory.GetCurrentDirectory() + "/document/TEMPSCORE.xlsx";

            outBook.Open(path);
            //获得一个worksheet
            Worksheet outSheet = outBook.Worksheets[0];

            //获得worksheet中的cell集合
            Cells outCells = outSheet.Cells;

            //填充数据
            for (int i = 0; i < gridView.RowCount; i++)
            {
                outCells[i + 5, 0].PutValue(i + 1);
                for (int j = 1; j < gridView.ColumnCount; j++)
                {
                    outCells[i + 5, j].PutValue(gridView[j, i].Value.ToString());
                }
            }

            //保存文件
            string fileName = savePath;

            outBook.Save(fileName, FileFormatType.Excel2003);

            return true;
        }

        /// <summary>
        /// 保存学生素养考核明细表
        /// </summary>
        /// <param name="evaluation"></param>
        /// <param name="savePath"></param>
        public static void SaveStuEvaluation(Evaluation evaluation, string savePath)
        {
            try
            {
                string path = System.IO.Directory.GetCurrentDirectory() + "/document/stuTemp.xls";
                Workbook workbook = new Workbook();
                workbook.Open(path);
                //获得一个worksheet
                Worksheet worksheet = workbook.Worksheets[0];
                //获得worksheet中的cell集合
                Cells cells = worksheet.Cells;
                if (cells == null)
                    return;
                //++++++++++++++++++++++++++++++++++++++++++
                cells[0, 0].PutValue(string.Format("讯飞教育实训学员{0}素养考核明细表", evaluation.StudentName));

                //意志力
                cells[5, 8].PutValue(evaluation.EvaluationItemMap[EnumUtils.GetName(EvaluationType.YiZhiLI)].Score);
                //压力测试
                cells[13, 8].PutValue(evaluation.EvaluationItemMap[EnumUtils.GetName(EvaluationType.YaLi)].Score);
                //自制力
                cells[20, 8].PutValue(evaluation.EvaluationItemMap[EnumUtils.GetName(EvaluationType.ZiZhiLi)].Score);
                //心理承受能力测评
                cells[25, 8].PutValue(evaluation.EvaluationItemMap[EnumUtils.GetName(EvaluationType.XinLiChenShouNengLi)].Score);
                //心理适应能力测评
                cells[31, 8].PutValue(evaluation.EvaluationItemMap[EnumUtils.GetName(EvaluationType.XinLiShiYinNengLi)].Score);
                //自主性测评
                cells[37, 8].PutValue(evaluation.EvaluationItemMap[EnumUtils.GetName(EvaluationType.ZiZhuXing)].Score);
                //乐观指数测评
                cells[43, 8].PutValue(evaluation.EvaluationItemMap[EnumUtils.GetName(EvaluationType.LeGuangZhiShu)].Score);
                // 交际能力测评
                cells[49, 8].PutValue(evaluation.EvaluationItemMap[EnumUtils.GetName(EvaluationType.JiaoJiNengLi)].Score);
                // 智力测评
                cells[55, 8].PutValue(evaluation.EvaluationItemMap[EnumUtils.GetName(EvaluationType.ZhiLi)].Score);
                //  职业性格分析
                cells[69, 8].PutValue(evaluation.EvaluationItemMap[EnumUtils.GetName(EvaluationType.ZhiYeXingGe)].result);
                //++++++++++++++++++++++++++++++++++++++++++
                //保存文件
                string fileName = savePath + string.Format("\\{0}素养考核明细表.xls", evaluation.StudentName);

                workbook.Save(fileName, FileFormatType.Excel2003);

            }
            catch (Exception ex)
            {

            }
        }

        #endregion

        #region  自定义辅助函数

        /// <summary>
        /// 得到学生的综合测评
        /// </summary>
        /// <param name="cells"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        private static Evaluation GetEvaluation(Cells cells, int rowIndex)
        {
            Evaluation evaluation = new Evaluation();
            try
            {
                if (cells[rowIndex + 1, 0].Value == null)
                {
                    MessageBox.Show(string.Format("第{0}行，第{1}列没有数据！", rowIndex + 1, 1));
                    return null;
                }
                //学生账号
                evaluation.AccountName = cells[rowIndex + 1, 0].Value.ToString();
                //学生姓名
                evaluation.StudentName = cells[rowIndex + 1, 1].Value.ToString();
                //学生答案
                List<StuAnswer> stuAnswerList = new List<StuAnswer>();
                //去除前面两列（账号，姓名，index从2开始）
                for (int i = 2; i < cells.MaxColumn; i++)
                {
                    stuAnswerList.Add(GetStudentAnswer(rowIndex, i, cells));
                }
                evaluation.StuAnswerList = stuAnswerList;
            }
            catch (Exception)
            {

            }
            return evaluation;
        }

        /// <summary>
        /// 学生答案
        /// </summary>
        /// <param name="rowIndex">行index</param>
        /// <param name="colIndex">列index</param>
        /// <param name="cells">数据单元集合d</param>
        /// <returns></returns>
        private static StuAnswer GetStudentAnswer(int rowIndex, int colIndex, Cells cells)
        {
            StuAnswer StuAnswer = new StuAnswer();
            StuAnswer.ExamId = colIndex - 1;
            StuAnswer.AnswerContent = cells[rowIndex + 1, colIndex].Value != null ? cells[rowIndex + 1, colIndex ].Value.ToString() : string.Empty;
            return StuAnswer;
        }

        #endregion
    }
}
